﻿// -----------------------------------------------------------------------
// <copyright file="DataImportDAC.cs" company="Microsoft">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

namespace DataImportService
{
    using System;
    using System.Data.SqlClient;
    using System.Data.Common;
    using System.Data;
    using DBHelper;
    public class DataImportDAC
    {
        private string _connectionString;

        public DataImportDAC(string dbConnectionString)
        {
            _connectionString = dbConnectionString;
        }
        /// <summary>
        /// Import data into a Employee staging table 
        /// </summary>
        /// <param name="adamData"></param>
        /// <returns></returns>
        public bool BulkImportToEmployeeStagingData(DataTable adamData)
        { 
           
                using (SqlHelper sqlHelper = new SqlHelper(_connectionString))
                {
                //Delete staging table data
                sqlHelper.AddOutputParameter("@ErrorCode", SqlDbType.Int);
                sqlHelper.ExecuteNonQuery("Staging_Employee_ClearData", CommandType.StoredProcedure, SqlHelper.ConnectionState.CloseOnExit);

                if (sqlHelper.Command.Parameters["@ErrorCode"].Value == DBNull.Value)
                {
                    SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString);
                    bulkCopy.DestinationTableName = "dbo.CMT_Employee_Staging";

                        bulkCopy.ColumnMappings.Add("GUI", "GUI");
                        bulkCopy.ColumnMappings.Add("GPN", "GPN");
                        bulkCopy.ColumnMappings.Add("LAST_NAME", "LAST_NAME");
                        bulkCopy.ColumnMappings.Add("MIDDLE_NAME", "MIDDLE_NAME");
                        bulkCopy.ColumnMappings.Add("FIRST_NAME", "FIRST_NAME");
                        bulkCopy.ColumnMappings.Add("PREFERRED_NAME", "PREFERRED_NAME");
                        bulkCopy.ColumnMappings.Add("Rank", "Rank");
                        bulkCopy.ColumnMappings.Add("BusinessUnit", "BusinessUnit");
                        bulkCopy.ColumnMappings.Add("OperatingUnit", "OperatingUnit");
                        bulkCopy.ColumnMappings.Add("ManagementUnit", "ManagementUnit");
                        bulkCopy.ColumnMappings.Add("SMU", "SMU");
                        bulkCopy.ColumnMappings.Add("NetworkID", "NetworkID");
                        bulkCopy.ColumnMappings.Add("NAME", "NAME");
                        bulkCopy.ColumnMappings.Add("InternetEmail", "InternetEmail");
                        bulkCopy.ColumnMappings.Add("CountryCode", "CountryCode");
                        bulkCopy.ColumnMappings.Add("EmploymentStatus", "EmploymentStatus");
                        bulkCopy.ColumnMappings.Add("WhenChanged", "WhenChanged");

                        bulkCopy.BulkCopyTimeout = 3600;
                        bulkCopy.BatchSize = 500;

                        //Bulkcopy to staging table                       
                        bulkCopy.WriteToServer(adamData);
                        return true;
                }
                else
                {
                   return false;
                }
                }
        }


        /// <summary>
        /// Import data into a country table 
        /// </summary>
        /// <param name="adamData"></param>
        /// <returns></returns>
        public bool ImportCountryData(DataTable countryData, out int insertedRecordsCount)
        {
            using (SqlHelper sqlHelper = new SqlHelper(_connectionString))
            {
                sqlHelper.AddOutputParameter("@ErrorCode", SqlDbType.Int);
                sqlHelper.AddOutputParameter("@InsertedRecordsCount", SqlDbType.Int);
                SqlParameter param = new SqlParameter();
                param = (sqlHelper.Command as SqlCommand).Parameters.AddWithValue("@CountryTableType", countryData);
                param.SqlDbType = SqlDbType.Structured;
                param.TypeName = "dbo.CountryTableType";

                sqlHelper.ExecuteNonQuery("Update_vwCountry_MC", CommandType.StoredProcedure, SqlHelper.ConnectionState.CloseOnExit);

                if ( Convert.ToInt32(sqlHelper.Command.Parameters["@ErrorCode"].Value) == 0)
                {
                    insertedRecordsCount = Convert.ToInt32(sqlHelper.Command.Parameters["@InsertedRecordsCount"].Value);
                    return true;
                }
                else
                {
                    insertedRecordsCount = 0;
                    return false;
                }
            }
        }
        /// <summary>
        /// Update Employee table with data from ADAM
        /// </summary>
        /// <param name="insertedRecordsCount">Number of rows inserted</param>
        /// <param name="updatedRecordsCount">Number of rows updated</param>
        /// <param name="deletedRecordsCount">Number of rows deleted</param>
        /// <returns></returns>
        public bool UpdateEmployeeData(out int recordsCount)
        {
            using (SqlHelper sqlHelper = new SqlHelper(_connectionString))
            {
                sqlHelper.AddOutputParameter("@ErrorCode", SqlDbType.Int);
                sqlHelper.AddOutputParameter("@RecordsCount", SqlDbType.Int);
                //sqlHelper.AddOutputParameter("@UpdatedRecordsCount", SqlDbType.Int);
                //sqlHelper.AddOutputParameter("@DeletedRecordsCount", SqlDbType.Int);
                sqlHelper.ExecuteNonQueryLongRequest("[dbo].[Update_Employee_ADAM]", CommandType.StoredProcedure, SqlHelper.ConnectionState.CloseOnExit);

                if ( Convert.ToInt32(sqlHelper.Command.Parameters["@ErrorCode"].Value) == 0)
                {
                    recordsCount = Convert.ToInt32(sqlHelper.Command.Parameters["@RecordsCount"].Value);
                    //updatedRecordsCount = Convert.ToInt32(sqlHelper.Command.Parameters["@UpdatedRecordsCount"].Value);
                    //deletedRecordsCount = Convert.ToInt32(sqlHelper.Command.Parameters["@DeletedRecordsCount"].Value);
                    return true;
                }
                else
                {
                    recordsCount = 0;
                    //updatedRecordsCount = 0;
                    //deletedRecordsCount = 0;
                    return false;
                }
            }
        }

        /// <summary>
        /// Get last pull date from log table
        /// </summary>
        /// <returns></returns>
        public DateTime? getLastLDAPImportDate()
        {
            using (SqlHelper sqlHelper = new SqlHelper(_connectionString))
            {
                var lastImportedDate = sqlHelper.ExecuteScalar("SELECT max(Updated_Date) FROM DataImport_History", CommandType.Text, SqlHelper.ConnectionState.CloseOnExit);
                return lastImportedDate != null ? (lastImportedDate as DateTime?) : null;

            }
        }



    }
}
